import traceback

import clickhouse_sqlalchemy
import pymysql
import redis
import sys
import pymongo
import paramiko
import pandas as pd
from clickhouse_sqlalchemy import make_session
from sqlalchemy import create_engine
from conf.operationConfig import OperationConfig
from common.recordlog import logs
from common.two_dimension_data import print_table

conf = OperationConfig()


class ConnectMysql:
    def __init__(self):

        mysql_conf = {
            'host': conf.get_section_mysql('host'),
            'port': int(conf.get_section_mysql('port')),
            'user': conf.get_section_mysql('username'),
            'password': conf.get_section_mysql('password'),
            'database': conf.get_section_mysql('database')
        }

        try:
            self.conn = pymysql.connect(**mysql_conf, charset='utf-8')
            # cursor（游标）：执行 SQL 语句以及获取查询结果的接口
            self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
            logs.info("""成功连接到mysql---
            host:{host}
            port:{port}
            db:{database}
            """.format(**mysql_conf))
        except Exception as e:
            logs.error(f"except:{e}")

    def close(self):
        # 关闭数据库连接和游标
        if self.conn and self.cursor:
            self.conn.close()
            self.cursor.close()
        return True

    # 该函数不是析构函数，python里面的析构函数的函数名为__del__
    def delete(self, sql):
        try:
            self.cursor.execute(sql)
            self.conn.commit()
            logs.info('删除成功')
        except Exception as e:
            logs.error(e)
        finally:
            # 无论try中的代码是否抛出异常，finally块的代码都会被执行
            self.close()

    def query_all(self, sql):
        # 执行 SQL 查询并处理结果
        try:
            # 执行SQL查询语句
            self.cursor.execute(sql)
            # 提交事务
            self.conn.commit()
            # 获取所有查询结果
            res = self.cursor.fetchall()

            key = ''
            values = []
            for item in res:
                keys = list(item.keys())

            for ite in res:
                values.append(list(ite.values()))

            for val in values:
                lst_format = [
                    val
                ]
                return lst_format

        except Exception as e:
            logs.error(e)
        finally:
            self.close()


class ConnectRedis:

    def __init__(self, ip=conf.get_section_redis("host"), port=conf.get_section_redis("port"), username=None,
                 passwd=None, db=conf.get_section_redis("db")):
        self.host = ip
        self.port = port
        self.username = username
        self.password = passwd
        self.db = db

        # 使用连接池方式，decode_responses=True可自动转为字符串
        logs.info(f"连接Redis--host:{ip},port:{port},user:{username},password:{passwd},db:{db}")

        try:
            pool = redis.ConnectionPool(host=self.host, port=int(self.port), password=passwd)
            self.first_conn = redis.Redis(connection_pool=pool, decode_responses=True)
        except Exception:
            # traceback.format_exc():获取当前异常的完整堆栈跟踪信息，并将其格式化为字符串
            logs.error(str(traceback.format_exc()))

    def set_kv(self, key, value, ex=None):
        """
        把键值对存储到redis数据库里
        :param key:
        :param value:
        :param ex: 过期时间，秒
        :return:
        """
        try:
            return self.first_conn.set(name=key, value=value, ex=ex)
        except Exception:
            logs.error(str(traceback.format_exc()))

    def get_kv(self, name):
        try:
            return self.first_conn.get(name)
        except Exception:
            logs.error(str(traceback.format_exc()))

    def hash_kv(self, key, value, ex=None):
        """
        不懂这个函数的意思？？
        :param key:
        :param value:
        :param ex:
        :return:
        """
        try:
            return self.first_conn.set(name=key, value=value, ex=ex)
        except Exception:
            logs.error(str(traceback.format_exc()))

    def hash_hget(self, names, keys):
        """在name对应的hash中获取根据key获取value"""
        try:
            data = self.first_conn.hget(names, keys).decode()
            return data
        except Exception:
            logs.error(str(traceback.format_exc()))

    def hash_hmget(self, name, keys, *args):
        """在name对应的hash中获取多个keys的值"""
        if not isinstance(keys, list):
            raise ("keys应为列表")
        try:
            return self.first_conn.hmget(name, keys, *args)
        except Exception:
            logs.error(str(traceback.format_exc()))
